package com.cn.system.common.service.impl;

import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.transaction.Transactional;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.jpa.HibernateEntityManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.util.Assert;

import com.cn.system.common.service.BaseService;
import com.cn.system.common.util.DateUtil;
import com.cn.system.common.util.Page;

import net.sf.json.JSONArray;
import net.sf.json.JSONException;
import net.sf.json.JSONObject;

/**
 * 自定义sql 查询类
 * 
 * @author Administrator
 *
 */

@SuppressWarnings("unchecked")
@Transactional
public class BaseRepository<T> implements BaseService<T> {

	protected Logger logger = LoggerFactory.getLogger(getClass());

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	 @PersistenceContext
    private EntityManager em;
 
    private Class<T> clazz = null;
 
    public BaseRepository(){
    	Type type = this.getClass().getGenericSuperclass(); // generic 泛型
	    if(type instanceof ParameterizedType){
	    	 ParameterizedType pt = (ParameterizedType) type;
	    	 this.clazz = (Class<T>) pt.getActualTypeArguments()[0];
	    }
    }



	public <T> List<T> queryForList(final String queryString, final Object... params) {
		try {
			List mapList = new ArrayList();
			if (params != null && params.length > 0) {
				mapList = jdbcTemplate.queryForList(queryString, params);
			} else {
				mapList = jdbcTemplate.queryForList(queryString);
			}
			return mapList;
		} catch (Exception e) {
			logger.error("queryForList异常:" + queryString);
			e.printStackTrace();
			return null;
		}
	}

	public Map<String, Object> queryForMap(final String queryString, final Object... params) {
		try {
			Assert.hasText(queryString, "sql语句不正确!:" + queryString);
			List<Map<String, Object>> mapList = this.queryForList(queryString, params);
			if (mapList != null && mapList.size() > 0) {
				return mapList.get(0);
			} else {
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	public JSONArray queryForJSONArray(final String queryString, final Object... params) throws JSONException {
		try {
			final JSONArray jsonArray = new JSONArray();
			Assert.hasText(queryString, "sql语句不正确!:" + queryString);
			if (params != null && params.length > 0) {
				jdbcTemplate.query(queryString, params, new RowCallbackHandler() {
					@Override
					public void processRow(ResultSet resultSet) throws SQLException {
						ResultSetMetaData metaData = resultSet.getMetaData();
						int cols = metaData.getColumnCount();
						String[] columnName = new String[cols];
						JSONObject jsonObj = new JSONObject();
						for (int i = 0; i < columnName.length; i++) {
							columnName[i] = metaData.getColumnName(i + 1).toUpperCase();
							jsonObj.put(columnName[i],
									resultSet.getObject(columnName[i]) != null ? resultSet.getString(columnName[i])
											: "");
						}
						jsonArray.add(jsonObj);
					}
				});
			} else {
				jdbcTemplate.query(queryString, new RowCallbackHandler() {
					@Override
					public void processRow(ResultSet resultSet) throws SQLException {
						ResultSetMetaData metaData = resultSet.getMetaData();
						int cols = metaData.getColumnCount();
						String[] columnName = new String[cols];
						// String[] columnTypeName =new String[cols];
						JSONObject jsonObj = new JSONObject();
						for (int i = 0; i < columnName.length; i++) {
							columnName[i] = metaData.getColumnName(i + 1).toUpperCase();
							jsonObj.put(columnName[i],
									resultSet.getObject(columnName[i]) != null ? resultSet.getString(columnName[i])
											: "");
						}
						jsonArray.add(jsonObj);
					}
				});
			}
			return jsonArray;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	@Override
    public T save(T t) {
        em.persist(t);
        return t;
    }
 
    @Override
    public T update(T t) {
        return em.merge(t);
    }
 
 
    @Override
    public void delete(Serializable id) {
        T t = em.getReference(clazz, id);
        em.remove(t);
    }
 
    @Override
    public int executeUpdate(String jpql, Object... obj) {
        Query query = em.createQuery(jpql);
        if(obj.length > 0){
            for (int i = 0; i < obj.length; i++) {
                query.setParameter((i+1),obj[i]);
            }
        }
        return query.executeUpdate();
    }
 
    @Override
    public T load(Serializable id) {
        return em.find(clazz, id);
    }
 
    @Override
    public T load(String jpql, Object... obj) {
        try{
            Query query = em.createQuery(jpql);
            if(obj.length > 0){
                for (int i = 0; i < obj.length; i++) {
                    query.setParameter((i+1),obj[i]);
                }
            }
            return (T) query.getSingleResult();
        }catch (Exception e){
          return null;
        }
    }
 
    @Override
    public List<T> findAll() {
        return em.createQuery("from "+clazz.getSimpleName()).getResultList();
    }
 
    @Override
    public List<T> find(String jpql, Object... obj) {
        try{
            Query query = em.createQuery(jpql);
            if(obj.length > 0){
                for (int i = 0; i < obj.length; i++) {
                    query.setParameter((i+1),obj[i]);
                }
            }
           return query.getResultList();
        }catch (Exception e){
            return null;
        }
    }
 
    @Override
    public Object findByAggregate(String jpql, Object... obj) {
        Query query = em.createQuery(jpql);
        if(obj.length > 0){
            for (int i = 0; i < obj.length; i++) {
                query.setParameter((i+1),obj[i]);
            }
        }
        Object result = query.getSingleResult();
        return result;
    }
 
    @Override
    public int count() {
        Long num = (Long) em.createQuery("select count(*) from "+clazz.getSimpleName()).getSingleResult();
        return num.intValue();
    }
 
    @Override
    public int count(String jpql,Object... obj) {
        try{
            Query query = em.createQuery(jpql);
            if(obj.length > 0){
                for (int i = 0; i < obj.length; i++) {
                    query.setParameter((i+1),obj[i]);
                }
            }
            Long num = (Long)query.getSingleResult();
            return num.intValue();
        }catch (Exception e){
            return 0;
        }
    }
 
    @Override
    public List<T> findPage(Integer firstIndex, Integer maxResults) {
        return em.createQuery("from "+clazz.getSimpleName()).setFirstResult(firstIndex).setMaxResults(maxResults).getResultList();
    }
 
    @Override
    public List<T> findPage(Integer firstIndex, Integer maxResults,String jpql,Object... obj) {
        try{
            Query query = em.createQuery(jpql);
            if(obj.length > 0){
                for (int i = 0; i < obj.length; i++) {
                    query.setParameter((i+1),obj[i]);
                }
            }
            query.setFirstResult(firstIndex).setMaxResults(maxResults);
            return query.getResultList();
        }catch (Exception e){
            return null;
        }
 
    }
 
    @Override
    public Page findPage(Page page, String jpql, Object... obj) {
        Query query = em.createQuery(jpql);
        if(obj.length > 0){
            for (int i = 0; i < obj.length; i++) {
                query.setParameter((i+1),obj[i]);
            }
        }
        int total = ((Long)query.getSingleResult()).intValue();
        page.setTotalCount(total);
        query.setFirstResult(page.getFirst()).setMaxResults(page.getPageSize());
        List<T> list = query.getResultList();
       // page.setItems(list);
        return page;
    }



    @SuppressWarnings("deprecation")
	public int executeSQL(final String sql, Object... params) {
		return em.createQuery(sql).executeUpdate();
	}



	@Override
	public List<LinkedHashMap<String, Object>> queryForListLinkedHashMap(String queryString, Object... params)
			throws Exception {
		// TODO Auto-generated method stub
		Assert.hasText(queryString,"sql语句不正确!:"+queryString);
		final List<LinkedHashMap<String,Object>> mapList=new ArrayList<LinkedHashMap<String,Object>>();
		if(params!=null && params.length>0){
			jdbcTemplate.query(queryString,params,new RowCallbackHandler() {
				@Override
				public void processRow(ResultSet resultSet) throws SQLException {
					ResultSetMetaData metaData = resultSet.getMetaData();
					int cols = metaData.getColumnCount();
					String[] columnName = new String[cols];
					LinkedHashMap<String,Object> item=new LinkedHashMap<String,Object>();
					for (int i = 0; i < columnName.length; i++) {
						columnName[i] = metaData.getColumnName(i + 1).toUpperCase();
						if(resultSet.getString(columnName[i])!=null && !"".equals(resultSet.getString(columnName[i]))){
							//特殊处理BigDecimal字段类型(ORACLE数据库number映射过来的为BigDecimal);
							if(BigDecimal.class.getName().equals(metaData.getColumnClassName(i+1))){
								item.put(columnName[i], resultSet.getBigDecimal(columnName[i]));
							}else if(Timestamp.class.getName().equals(metaData.getColumnClassName(i+1))){
								Timestamp sd=resultSet.getTimestamp(columnName[i]);
								if(sd!=null){
									item.put(columnName[i],DateUtil.getCurrentTime(sd));
								}
							}else{
								item.put(columnName[i], resultSet.getString(columnName[i]));
							}
						}else{
							item.put(columnName[i], "");
						}
					}
					mapList.add(item);
				}
			});
		}else{
			jdbcTemplate.query(queryString,new RowCallbackHandler() {
				@Override
				public void processRow(ResultSet resultSet) throws SQLException {
					ResultSetMetaData metaData = resultSet.getMetaData();
					int cols = metaData.getColumnCount();
					String[] columnName = new String[cols];
					LinkedHashMap<String,Object> item=new LinkedHashMap<String,Object>();
					for (int i = 0; i < columnName.length; i++) {
						columnName[i] = metaData.getColumnName(i + 1).toUpperCase();
						if(resultSet.getString(columnName[i])!=null && !"".equals(resultSet.getString(columnName[i]))){
							//特殊处理BigDecimal字段类型(ORACLE数据库number映射过来的为BigDecimal);
							if(BigDecimal.class.getName().equals(metaData.getColumnClassName(i+1))){
								item.put(columnName[i], resultSet.getBigDecimal(columnName[i]));
							}else if(Timestamp.class.getName().equals(metaData.getColumnClassName(i+1))){
								Timestamp sd=resultSet.getTimestamp(columnName[i]);
								if(sd!=null){
									item.put(columnName[i],DateUtil.getCurrentTime(sd));
								}
							}else{
								item.put(columnName[i], resultSet.getString(columnName[i]));
							}
						}else{
							item.put(columnName[i], "");
						}
					}
					mapList.add(item);
				}
			});
		}
		return mapList;
	}



	@Override
	public void saveOrUpdate(Object entity) {
		// TODO Auto-generated method stub
		Assert.notNull(entity, "entity不能为空");
		em.persist(entity);
	}



	@Override
	public <T> T get(Class<T> clazz, Serializable id) {
		// TODO Auto-generated method stub
		Assert.notNull(id, "id不能为空");
		Assert.notNull(clazz, "clazz不能为空");
		return (T) em.find(clazz, id);
	}


	@Override
	public String queryForString(String queryString, Object... params) {
		// TODO Auto-generated method stub
		try{
			Assert.hasText(queryString,"sql语句不正确!:"+queryString);
			if(params!=null && params.length>0){
				return (String)jdbcTemplate.queryForObject(queryString, params, new RowMapper() {
					@Override
					public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
						String str = rs.getString(1);
					    return StringUtils.isBlank(str) ? "" : str;
					}
				});
			}else{
				return (String)jdbcTemplate.queryForObject(queryString, new RowMapper() {
					@Override
					public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
						String str = rs.getString(1);
					    return StringUtils.isBlank(str) ? "" : str;
					}
				});
			}
		}catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}



	@Override
	public Session getCurrentSession() {
		// TODO Auto-generated method stub
		HibernateEntityManager hEntityManager = (HibernateEntityManager)em;
		Session session = (Session)hEntityManager.getSession();
		return session;
	}



	@Override
	public SessionFactory getSessionFactory() {
		// TODO Auto-generated method stub
		HibernateEntityManager hEntityManager = (HibernateEntityManager)em;
		Session session = (Session)hEntityManager.getSession();
		return session.getSessionFactory();
	}
    
}
